package com.mj.haier.rrskx.service;

import java.util.Collection;
import java.util.Collections;
import java.util.List;
import java.util.Optional;

import javax.persistence.EntityManager;
import javax.persistence.PersistenceContext;
import javax.persistence.Query;

import com.mj.haier.rrskx.domain.AzEorderTitle;
import com.mj.haier.rrskx.domain.BdSwitcher;
import com.mj.haier.rrskx.repository.BdSwitcherRepository;

import org.slf4j.Logger;
import org.slf4j.LoggerFactory;
import org.springframework.stereotype.Service;
import org.springframework.transaction.annotation.Transactional;

/**
 * 同步快线库安装单信息
 * 
 * @author ycm
 */
@Service
@Transactional
public class AzEorderService {

    private final Logger log = LoggerFactory.getLogger(AzEorderService.class);

    @PersistenceContext
    private EntityManager entityManager;

    private final BdSwitcherRepository bdSwitcherRepository;

    public AzEorderService(BdSwitcherRepository bdSwitcherRepository){
        this.bdSwitcherRepository = bdSwitcherRepository;
    }

    /**
     * 
     * 获取待同步的安装单信息
     * 
     * @param switcherValue 工贸编码
     * @param limit 限制, 默认 1000行
     * 
     */
    public List<AzEorderTitle> fetchSyncAzEorderTitle(String switcherValue, Integer limit){

        if(0 == limit){
            limit = 1000;
        }
        String TABLE_AZ_ORDER_TITLE_FIN = "az_order_title_fin";
        String TABLE_AZ_ORDER_TITLE_MAIN = "az_order_title_main";
        String TABLE_AZ_ORDER_TITLE_PART = "az_order_title_part";
        String suffix = "_default";

        Optional<BdSwitcher> optional = bdSwitcherRepository.findBySwitcherValue(switcherValue);
        
        if(optional.isPresent()){
            suffix = optional.get().getSwitcherKey();   
        }
        TABLE_AZ_ORDER_TITLE_FIN = TABLE_AZ_ORDER_TITLE_FIN + suffix;
        TABLE_AZ_ORDER_TITLE_MAIN = TABLE_AZ_ORDER_TITLE_MAIN + suffix;
        TABLE_AZ_ORDER_TITLE_PART = TABLE_AZ_ORDER_TITLE_PART + suffix;

        StringBuilder sb = new StringBuilder(
            "SELECT\n" +
                        "\tf.ORDER_CODE AS order_code,\n" +
                        "\tf.branch_id AS trade_code,\n" +
                        "\tm.hcc_wo_id,\n" +
                        "\tm.customer_name,\n" +
                        "\tm.enter_time,\n" +
                        "\tm.service_info_id as service_info_id,\n" +
                        "\tm.source_order_ID as source_order_id,\n" +
                        // "\tf.server_info_code,\n" +
                        // "\tf.server_info_name,\n" +
                        "\tm.server_info_ID as server_info_code,\n" +
                        "\tm.server_name as server_info_name,\n" +
                        "\tf.product_no,\n" +
                        "\tm.product_name,\n" +
                        "\tf.product_model_no,\n" +
                        "\tm.modal_name as product_model_name,\n" +
                        "\tf.product_ser_no,\n" +
                        "\tf.product_ser_desc,\n" +
                        "\t'' as source_name,\n" +
                        "\ty.TWO_LEAVEL_CODE as source_name_code,\n" +
                        "\tp.haier_close_status,\n" +
                        "\tp.haier_close_time,\n" +
                        "\tp.net_employee_info_ID1 as net_employee_info_id_1,\n" +
                        "\tp.net_employee_info_ID2 as net_employee_info_id_2,\n" +
                        "\tp.car_percent,\n" +
                        "\tp.carsteam_percent,\n" +
                        "\tp.server_close_status,\n" +
                        "\tp.server_close_time,\n" +
                        "\ty.operate_cost,\n" +
                        "\ty.service_type_cost,\n" +
                        "\ty.traffic_cost,\n" +
                        "\ty.meterial_cost,\n" +
                        "\ty.subidy_cost,\n" +
                        "\ty.compensatory_cost,\n" +
                        "\ty.reward_cost,\n" +
                        "\ty.feee_total_cost as fee_total_cost,\n" +
                        "\tf.is_settle,\n" +
                        "\tf.is_published,\n" +
                        "\tf.cost_time\n" +
                        "FROM")
            .append(" ").append(TABLE_AZ_ORDER_TITLE_FIN).append(" ").append("f").append("\n")
            .append(" ").append("INNER JOIN t_monthly_statement y ON f.ORDER_CODE = y.order_code").append(" ").append("\n")
            .append(" ").append("LEFT JOIN").append(" ").append(TABLE_AZ_ORDER_TITLE_MAIN).append(" ").append("m ON f.ORDER_CODE = m.ORDER_CODE").append("\n")
            .append(" ").append("LEFT JOIN").append(" ").append(TABLE_AZ_ORDER_TITLE_PART).append(" ").append("p ON f.ORDER_CODE = p.ORDER_CODE").append("\n")
            .append(" ").append("WHERE f.is_settle = '1' limit").append(" ").append(limit);

        // StringBuilder sb = new StringBuilder(
        //     "SELECT\n" +
        //                 "\tm.ORDER_CODE AS order_code,\n" +
        //                 "\tm.branch_id AS trade_code,\n" +
        //                 "\tm.hcc_wo_id,\n" +
        //                 "\tm.customer_name,\n" +
        //                 "\tm.enter_time,\n" +
        //                 "\tm.service_info_id as service_info_id,\n" +
        //                 "\tm.source_order_ID as source_order_id,\n" +
        //                 "\tm.server_info_ID as server_info_code,\n" +
        //                 "\tm.server_name as server_info_name,\n" +
        //                 "\tf.product_no,\n" +
        //                 "\tm.product_name,\n" +
        //                 "\tf.product_model_no,\n" +
        //                 "\tm.modal_name as product_model_name,\n" +
        //                 "\tf.product_ser_no,\n" +
        //                 "\tf.product_ser_desc,\n" +
        //                 "\tm.source_name,\n" +
        //                 "\tm.source_name_code,\n" +
        //                 "\tp.haier_close_status,\n" +
        //                 "\tp.haier_close_time,\n" +
        //                 "\tp.net_employee_info_ID1 as net_employee_info_id_1,\n" +
        //                 "\tp.net_employee_info_ID2 as net_employee_info_id_2,\n" +
        //                 "\tp.car_percent,\n" +
        //                 "\tp.carsteam_percent,\n" +
        //                 "\tp.server_close_status,\n" +
        //                 "\tp.server_close_time,\n" +
        //                 "\ty.operate_cost,\n" +
        //                 "\ty.service_type_cost,\n" +
        //                 "\ty.traffic_cost,\n" +
        //                 "\ty.meterial_cost,\n" +
        //                 "\ty.subidy_cost,\n" +
        //                 "\ty.compensatory_cost,\n" +
        //                 "\ty.reward_cost,\n" +
        //                 "\ty.feee_total_cost as fee_total_cost,\n" +
        //                 "\tf.is_settle,\n" +
        //                 "\tf.is_published,\n" +
        //                 "\tf.cost_time\n" +
        //                 "FROM")
        //     .append(" ").append(TABLE_AZ_ORDER_TITLE_MAIN).append(" ").append("m").append("\n")
        //     .append(" ").append("INNER JOIN t_monthly_statement y ON m.ORDER_CODE = y.order_code").append(" ").append("\n")
        //     .append(" ").append("LEFT JOIN").append(" ").append(TABLE_AZ_ORDER_TITLE_PART).append(" ").append("p ON m.ORDER_CODE = p.ORDER_CODE").append("\n")
        //     .append(" ").append("LEFT JOIN").append(" ").append(TABLE_AZ_ORDER_TITLE_FIN).append(" ").append("f ON m.ORDER_CODE = f.ORDER_CODE").append("\n")
        //     .append(" ").append(" limit").append(" ").append(limit);

        // 创建本地sql查询实例
        Query azEorderQuery = entityManager.createNativeQuery(sb.toString(), AzEorderTitle.class);
        return azEorderQuery.getResultList() == null? Collections.<AzEorderTitle>emptyList(): azEorderQuery.getResultList();
    }


}
